'''
Created on 2011-10-26

@author: Administrator
'''
import os
import sqlite3
import urllib2
import datetime
from setting import *
from chart import *

def date2int(sdate):
    return sdate.year*10000+sdate.month*100+sdate.day
def str2date(sdate):
    return sdate
    return datetime.date(int(sdate[:4]),int(sdate[4:6]),int(sdate[6:8]))
def int2date(s):
    sdate=str(s)
    if sdate.find('/')<0 and sdate.find('-')<0 :
        return datetime.date(int(sdate[:4]),int(sdate[4:6]),int(sdate[6:8]))
    sdate=sdate.replace('/','-')
    s=[]
    s=sdate.split('-')
    return datetime.date(int(s[0]),int(s[1]),int(s[2]))
def myfloat(src):
    if len(src)==0:return 0.0
    else: return float(src)
class StockByDateDB():
    def __init__(self,somedate,reg):
        self.curdate=somedate
        dbfilename=os.path.join(stockbydatepath,reg,str(somedate)+'.db3')
#            dbfilename=os.path.join(stockbydatepath,str(somedate)+'.db3')
        self.db=sqlite3.connect(dbfilename)
        self.c=self.db.cursor()            
        self.createonetable('stocklistday', ' (code text,open real,close real,high real,low real,yearhigh real,yearlow real,ma20 real,ma60 real,ma120 real,radio real,PRIMARY KEY (code))')
        self.createonetable('stocklistweek', ' (code text,open real,close real,high real,low real,yearhigh real,yearlow real,ma20 real,ma60 real,ma120 real,radio real,PRIMARY KEY (code))')

#        self.updatestockdata(datalist)
    def createonetable(self,tablename,para=''):
        sql='create table if not exists '+tablename+para
        self.c.execute(sql)
        self.db.commit() 
    def updatestockdata(self,tabletype,datalist):
        if len(datalist)>0: 
            for r in datalist:                
                d=r
#                print 'd=',d
                sql='insert into '+tabletype+' (code,open,close,high,low,yearhigh,yearlow,ma20,ma60,ma120,radio) values(?,?,?,?,?,?,?,?,?,?,?)'
                self.c.execute(sql,(d[0],d[1],d[2],d[3],d[4],d[5],d[6],d[7],d[8],d[9],d[10]))
            self.db.commit()
    def focusdata(self):
        ret={}
        ret['ma120']=[['>120',0],['<120',0],['other',0]]
        ret['ma60']=[['>60',0],['<60',0],['other',0]]
        ret['ma20']=[['>20',0],['<20',0],['other',0]]
        ret['yearhighlow']=[['yearhigh',0],['yearlow',0],['other',0]]
        ret['2060120']=[['20>60>120',0],['20<60<120',0],['other',0]]
        
        sql='select code,close,high,low,ma20,ma60,ma120,yearhigh,yearlow from stocklist '
        self.c.execute(sql)
        result=self.c.fetchall()
        if result is None:return ret
        stocksum=len(result)
        for r  in result:
            (code,close,high,low,ma20,ma60,ma120,yearhigh,yearlow)=r
            print code
            #############################
            if ma20==0:ret['ma20'][2][1]=ret['ma20'][2][1]+1
            elif close>ma20:ret['ma20'][0][1]=ret['ma20'][0][1]+1
            else: ret['ma20'][1][1]=ret['ma20'][1][1]+1
            ############################
            if ma60==0:ret['ma60'][2][1]=ret['ma60'][2][1]+1
            elif close>ma60:ret['ma60'][0][1]=ret['ma60'][0][1]+1
            else: ret['ma60'][1][1]=ret['ma60'][1][1]+1
            ############################
            if ma120==0:ret['ma120'][2][1]=ret['ma120'][2][1]+1
            elif close>ma120:ret['ma120'][0][1]=ret['ma120'][0][1]+1
            else: ret['ma120'][1][1]=ret['ma120'][1][1]+1
            ############################
            if high>=yearhigh:ret['yearhighlow'][0][1]=ret['yearhighlow'][0][1]+1
            elif low<=yearlow:ret['yearhighlow'][1][1]=ret['yearhighlow'][1][1]+1
            else:ret['yearhighlow'][2][1]=ret['yearhighlow'][2][1]+1
            #########################################################
            if ma20> ma60 and ma60> ma120 and ma120>0:
                ret['2060120'][0][1]=ret['2060120'][0][1]+1
            elif ma120> ma60 and ma60> ma20 and ma20>0:
                ret['2060120'][1][1]=ret['2060120'][1][1]+1
            else:ret['2060120'][2][1]=ret['2060120'][2][1]+1
            ############################################
        return ret   
    def focusdataurl(self):
        result=self.focusdata()
        ret={}
        ct=Chart()
        for r in result.keys():
            ret[r]= ct.getchart_p(result[r],self.curdate)
        return ret
class StockDataDB():
    def __init__(self,job):
#        print 'stockdatadb.init:',job
        if job.find('_')>0:(self.stockcode,self.reg)=job.split('_')
        if job.find(':')>0:(self.stockcode,self.reg)=job.split(':')
        self.job=job
        dbpath=os.path.join(stockbycodepath,self.reg)
#        if not os.path.exists(dbpath):os.mkdir(dbpath)
        dbfilename=os.path.join(dbpath,self.stockcode+'.db3')   
#        print 'dbfilename:',dbfilename
        if os.path.exists(dbfilename) :        
            self.db=sqlite3.connect(dbfilename)
            self.c=self.db.cursor()
        else:
            self.db=sqlite3.connect(dbfilename)
            self.c=self.db.cursor()            
            self.createtable(' (date integer,open real,close real,high real,low real,yearhigh real,yearlow real,ma20 real,ma60 real,ma120 real,radio real,durations integer,PRIMARY KEY (date))')
#            self.createonetable(' (date integer,open real,close real,high real,low real,yearhigh real,yearlow real,ma20 real,ma60 real,ma120 real,radio real,durations integer,PRIMARY KEY (date))')
    def createtable(self,para=''):
        for tablename in ['kday','kweek']:
            sql='create table if not exists '+tablename+para
            self.c.execute(sql)
        self.db.commit()  
    def droptable(self): 
        for tablename in ['kday','kweek']:
            sql='drop table '+tablename
            self.c.execute(sql)
        self.db.commit()   
    def calconestockdata(self,datalist):  
        dstdatalist={}
        sdc=StockDataCalc()
        for srconedata in sorted(datalist.keys()):
            
            oneresult=sdc.addoneday(datalist[srconedata])
#            print datalist[srconedata],oneresult
            dstdatalist[srconedata]=oneresult
        return dstdatalist
    def loaddata(self,tabletype,startdate=0):
        if startdate==0:
            sql='select date,open,close,high,low,yearhigh,yearlow,ma20,ma60,ma120,radio from '+tabletype
            self.c.execute(sql)
        else:
#            print tabletype,type(tabletype)
            sql='select date,open,close,high,low,yearhigh,yearlow,ma20,ma60,ma120,radio from '+tabletype+' where date>?'
            self.c.execute(sql,(startdate,))
        ret=self.c.fetchall()
        rlist=[]
        for r in ret:
            oneline=[]
            for e in r:
                oneline.append(e)
            rlist.append(oneline)
            
        return rlist
    def getdatabydate(self,tabletype,somedate=''):
        if somedate=='':sql='select date,open,close,high,low,yearhigh,yearlow,ma20,ma60,ma120,radio from '+tabletype+' order by date desc limit 1'
        else:sql='select date,open,close,high,low,yearhigh,yearlow,ma20,ma60,ma120,radio from '+tabletype+' where date=?'
        self.c.execute(sql,(somedate,))
        ret=self.c.fetchone()
        return ret
    def getlastdatedata(self,tabletype):
        sql='select date,open,close,high,low from '+tabletype+' order by date desc limit 1'
        self.c.execute(sql)
        ret=self.c.fetchone()
        if ret is None:return None
        else:
            return {'date':ret[0],'open':ret[1],'close':ret[2],'high':ret[3],'low':ret[4]}
    def cutlastdate(self,tablename):
        dblastdata=self.getlastdatedata('kweek')
        upstartdate=dblastdata['date']
        sql='delete from '+tablename+' where date=?'
        self.c.execute(sql,(upstartdate,))
        self.db.commit()
        dblastdata=self.getlastdatedata('kweek')
        return dblastdata
    def updatestockdata(self):
        def getonestockdata():
            stockcode=self.stockcode
            stockdata={}
            content=open(os.path.join(rawcontentpath,self.job),'r').read()
            nlist=content.split('\n')
        #            print len(nlist)
            for n in nlist:
                t=n.split('"')
                if len(t)==5:
                    tdate=int(t[0])
                    openprice=round(myfloat(t[1].strip()),3)
                    closeprice=round(myfloat(t[2].strip()),3)
                    highprice=round(myfloat(t[3].strip()),3)
                    lowprice=round(myfloat(t[4].strip()),3)
                    stockdata[tdate]={'open':openprice,'close':closeprice,'high':highprice,'low':lowprice}
            return stockdata
        def updatekday(dlist,startdate=0):
            uprows=0
            if startdate>0:
                for r in dlist.keys():
                    if r<startdate-10000:
                        del dlist[r]
            datalistcalc=self.calconestockdata(dlist)
#            print '3:',datetime.datetime.now()
#            print len(datalist)
            for r in sorted(dlist.keys()):
#                print r,startdate
                if r>startdate:                    
                    
                    dr=datalistcalc[r]
    #                print r,d,dr
                    sql='insert into kday (date,open,close,high,low,yearhigh,yearlow,ma20,ma60,ma120,radio,durations) values(?,?,?,?,?,?,?,?,?,?,?,?)'
                    self.c.execute(sql,(r,dr['open'],dr['close'],dr['high'],dr['low'],dr['yearhigh'],dr['yearlow'],dr['ma20'],dr['ma60'],dr['ma120'],dr['radio'],dr['durations']))
                    uprows=uprows+1
            self.db.commit()  
            return uprows
        def updatekweek(wlist,startdate=0):
            uprows=0
            weekdatalistcalc=self.calconestockdata(wlist)
            for r in sorted(wlist.keys()):
                if r>startdate: 
                    dr=weekdatalistcalc[r]
        #                print r,d,dr
                    sql='insert into kweek (date,open,close,high,low,yearhigh,yearlow,ma20,ma60,ma120,radio,durations) values(?,?,?,?,?,?,?,?,?,?,?,?)'
                    self.c.execute(sql,(r,dr['open'],dr['close'],dr['high'],dr['low'],dr['yearhigh'],dr['yearlow'],dr['ma20'],dr['ma60'],dr['ma120'],dr['radio'],dr['durations']))
                    uprows=uprows+1
            self.db.commit() 
            return uprows                 
        uprows=0
        datalist={}
        weekdatalist={}
#        datalistcalc={}
#        print '1:',datetime.datetime.now()
        datalist=getonestockdata()
        weekdatalist=stockweeklist.day2week(datalist)
        #######################################
        dblastdata=self.getlastdatedata('kday')
#        print 'dblastdata:',self.job,dblastdata
        if dblastdata is None:
            print 'dblastdata is None'
            ########################################
            rs=updatekday(datalist)
            print self.job,'kday update rows:',rs
            ##############################################
            rs=updatekweek(weekdatalist)
            print self.job,'kweek update rows:',rs  
        else:
            tday=datalist[dblastdata['date']]          
            if dblastdata['open']==tday['open'] and dblastdata['close']==tday['close'] and dblastdata['high']==tday['high'] and dblastdata['low']==tday['low']:
                #kday add and kweek delete one and add other
                upstartdate=dblastdata['date']
                ########################################
                rs=updatekday(datalist,upstartdate)
#                print self.job,'kday update rows:',rs
                ##############################################
                dblastdata=self.cutlastdate('kweek')
                if dblastdata is None:upstartdate=0
                else:upstartdate=dblastdata['date']
    #            print upstartdate            
                rs=updatekweek(weekdatalist,upstartdate)
#                print self.job,'kweek update rows:',rs
            else:#drop
                self.droptable()
                print self.job,'drop tables'
                self.createtable(' (date integer,open real,close real,high real,low real,yearhigh real,yearlow real,ma20 real,ma60 real,ma120 real,radio real,durations integer,PRIMARY KEY (date))')
                ########################################
                rs=updatekday(datalist)
#                print self.job,'kday update rows:',rs
                ##############################################
                rs=updatekweek(weekdatalist)
#                print self.job,'kweek update rows:',rs

class StockWeek():
    def __init__(self):
        self.weeklist=self.createweeklist()
        pass
    def createweeklist(self):
        startday=int2date(20000101)
        endday=int2date(20500101)
#        print startday,endday,(startday-endday).days,startday.weekday()
        daylist=[]
        weeklist={}
        weeknum=1
        while endday>startday:
            if endday.weekday()==6:weeknum=weeknum+1
            if endday.weekday()<5:                
                weeklist[date2int(endday)]=weeknum
                      
            endday=endday-datetime.timedelta(days=1)
        return weeklist
        
    def day2week(self,srclist):
        def getoneweekdata(dlist):
            firstday=min(dlist)
            lastday=max(dlist)
            hlist=[]
            llist=[]
            for d in dlist:
#                print d,type(d),srclist[d]
                hlist.append(srclist[d]['high'])
                llist.append(srclist[d]['low'])
            return {'open':srclist[firstday]['open'],'close':srclist[lastday]['close'],'high':max(hlist),'low':min(llist)}
#        weeklist=self.createweeklist()
#        print weeklist[20061109]
        datelist={}
#        datelist[0]=0
        for src in sorted(srclist.keys()):
            nsrc=int(src)
#            print src,len(datelist),type(src)
            datelist[nsrc]=self.weeklist[nsrc]
        tlist={}
        for e in range(0,15000):
            tlist[e]=[]
        for oneday in datelist.keys():
            tlist[datelist[oneday]].append(oneday)
        wlist={}
        for t in tlist.keys():#            
            if len(tlist[t])>0:
                wlist[max(tlist[t])]=getoneweekdata(tlist[t])
        return wlist
stockweeklist=StockWeek()
class StockDataCalc():
    #yearhigh ,yearlow ,ma20 ,ma60 ,ma120 ,radio ,durations
    def __init__(self):
        self.srcdatalist=[]
        self.dstdatalist=[]
        self.closelist=[]
        self.openlist=[]
        self.lowlist=[]
        self.highlist=[]
        self.datenum=0
    def getyearhighandlow(self):
        days=200
        if self.datenum>days:
            yearhigh=max(self.highlist[self.datenum-days:])
            yearlow=min(self.lowlist[self.datenum-days:])
        else:
            yearhigh=max(self.highlist)
            yearlow=min(self.lowlist)    
        return (yearhigh,yearlow)  
    def getma(self,v):
        mv=0
        if self.datenum<v:return mv
        return round(sum(self.closelist[self.datenum-v:])/v,3)
    def getradio(self):
#        print 'getradio:',self.datenum
        if self.datenum>1:
            pre=self.closelist[self.datenum-2]
#            print 'getradio:',pre,self.closelist[self.datenum-1]
            if pre==0:return 0
            else:
                return round(self.closelist[self.datenum-1]/pre-1,3)
        return 0
    def getdurations(self):
        return 0
    def addoneday(self,onedata):
#        print 'onedata:',onedata     
        self.openlist.append(onedata['open'])
        self.closelist.append(onedata['close'])
        self.highlist.append(onedata['high'])
        self.lowlist.append(onedata['low'])
        self.datenum=self.datenum+1
        onedayresult={}
        onedayresult['open']=onedata['open']
        onedayresult['close']=onedata['close']
        onedayresult['high']=onedata['high']
        onedayresult['low']=onedata['low']
        onedayresult['yearhigh']=0.0
        onedayresult['yearlow']=0.0
        onedayresult['ma20']=0.0
        onedayresult['ma60']=0.0
        onedayresult['ma120']=0.0
        onedayresult['radio']=0.0
        onedayresult['durations']=0.0
#        for e in range(0,7):
#            onedayresult.append(0.0)     
        if self.datenum==0:pass
        else:
            onedayresult['yearhigh'],onedayresult['yearlow']=self.getyearhighandlow()
            onedayresult['ma20']=self.getma(20)
            onedayresult['ma60']=self.getma(60)
            onedayresult['ma120']=self.getma(120)
            onedayresult['radio']=self.getradio()
            onedayresult['durations']=self.getdurations()          
           
        return onedayresult
    def calc(self):
        pass
if __name__ == '__main__':
    print 'this is stockdata run'
    stockcode='WATG_US'
    sdb=StockDataDB(stockcode)
#    print sdb.getlastdatedata('kday')
    sdb.updatestockdata()
#    somedate='20111104'
#    sd=StockByDateDB(somedate)
#    urllist={}
#    urllist=sd.focusdataurl()
#    fname='c:\\temp.html'
#    f=open(fname,'w')
#    f.write('<html><body>')
#    for s in urllist.keys():
#        f.write('<img src="')
#        f.write (urllist[s])
#        f.write('">')
#        f.write('<br>')
#    f.write('</body></html>')

#    sd.updatestockdata()
#    dlist=sd.getdatabydate('20111103')
#    dlist=sd.loaddata()
#    print dlist
    
